from sqlalchemy import create_engine,MetaData,Column,Integer,String,Date,Table,ForeignKey,Float;
import os;

dbpath = "sqlite:///" + os.path.abspath('../db/dinner.db').replace('\\','\\\\');
db=create_engine(dbpath)
db.echo = True
metadata=MetaData(db)

table_company = Table('t_company',metadata,
                      Column('id',Integer,primary_key = True),
                      Column('name',String(40)),
                      Column('intro',String(65535))
                      )
if not table_company.exists() :
    table_company.create()

table_project = Table('t_project',metadata,
                      Column('id',Integer,primary_key = True),
                      Column('name',String(40)),
                      Column('intro',String(65536)),
                      Column('company_id',Integer,ForeignKey('t_company.id'),nullable = False)
                      )
if not table_project.exists():
    table_project.create()

table_employee = Table('t_employee',metadata,
                       Column('id',Integer,primary_key = True),
                       Column('name',String(40)),
                       Column('gender',Integer),
                       Column('project_id',Integer,ForeignKey('t_project.id'),nullable = False)
                       )
if not table_employee.exists():
    table_employee.create()

table_delivery = Table('t_delivery',metadata,
                       Column('id',Integer,primary_key = True),
                       Column('name',String(40)),
                       Column('address',String(100)),
                       Column('phone',String(100))
                       )
if not table_delivery.exists():
    table_delivery.create()

table_dish = Table('t_dish',metadata,
                   Column('id',Integer,primary_key = True),
                   Column('name',String(40)),
                   Column('price',Float),
                   Column('delivery_id',Integer,ForeignKey('t_delivery.id'),nullable = False)
                   )
if not table_dish.exists():
    table_dish.create()

table_order = Table('t_order',metadata,
                    Column('id',Integer,primary_key = True),
                    Column('ip',String(40)),
                    Column('employee_id',Integer,ForeignKey('t_employee.id'),nullable = False),
                    Column('dish_id',Integer,ForeignKey('t_dish.id'),nullable = False),
                    Column('order_time',Date)
                    )
if not table_order.exists():
    table_order.create()